﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using HRM.DAL.DAO;
using HRM.DAL.Report;

namespace HRM.DAL.Gateway
{
    public class TrainingGateway:DBGateway
    {
        public bool SaveTraining(Training training)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("insert into tblTraining values(@Name,@Department,@Trainer,@Topics,@Venue,@sDate,@eDate,@Description)");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("Name", SqlDbType.VarChar).Value = training.Name;
                CommandObj.Parameters.Add("Department", SqlDbType.VarChar).Value = training.Department;
                CommandObj.Parameters.Add("Trainer", SqlDbType.VarChar).Value = training.Trainer;
                CommandObj.Parameters.Add("Topics", SqlDbType.VarChar).Value = training.Topics;
                CommandObj.Parameters.Add("Venue", SqlDbType.VarChar).Value = training.Venue;
                CommandObj.Parameters.Add("sDate", SqlDbType.DateTime).Value = training.StartDate;
                CommandObj.Parameters.Add("eDate", SqlDbType.DateTime).Value = training.EndDate;
                CommandObj.Parameters.Add("Description", SqlDbType.VarChar).Value = training.Description;
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error occured during Saving Training Information");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public bool UpdateTrainingInfo(int id,Training training)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("Update tblTraining set Name=@Name,Department=@Department,Trainer=@Trainer,Topics=@Topics,Venue=@Venue,StratDate=@sDate,EndDate=@eDate,Description=@Description where Id='" + id + "'");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("Name", SqlDbType.VarChar).Value = training.Name;
                CommandObj.Parameters.Add("Department", SqlDbType.VarChar).Value = training.Department;
                CommandObj.Parameters.Add("Trainer", SqlDbType.VarChar).Value = training.Trainer;
                CommandObj.Parameters.Add("Topics", SqlDbType.VarChar).Value = training.Topics;
                CommandObj.Parameters.Add("Venue", SqlDbType.VarChar).Value = training.Venue;
                CommandObj.Parameters.Add("sDate", SqlDbType.DateTime).Value = training.StartDate;
                CommandObj.Parameters.Add("eDate", SqlDbType.DateTime).Value = training.EndDate;
                CommandObj.Parameters.Add("Description", SqlDbType.VarChar).Value = training.Name;
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error occured during Updateing Training Information");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public List<Training> GetAllTrainings()
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"select * from tblTraining order by Name";
                SqlDataReader reader = CommandObj.ExecuteReader();
                List<Training> trainings=new List<Training>();
                while (reader.Read())
                {
                    Training training=new Training();
                    training.Id = Convert.ToInt32(reader[0]);
                    training.Name = reader[1].ToString();
                    training.Department = reader[2].ToString();
                    training.Trainer = reader[3].ToString();
                    training.Topics = reader[4].ToString();
                    training.Venue = reader[5].ToString();
                    DateTime sdt = Convert.ToDateTime(reader[6]);
                    training.StartDate = sdt.ToShortDateString();
                    DateTime edt = Convert.ToDateTime(reader[7]);
                    training.EndDate = edt.ToShortDateString();
                    training.Description = reader[8].ToString();
                    trainings.Add(training);
                }
                return trainings;
            }
            catch (Exception)
            {
                throw new Exception("Error occured during Retriving Training Information");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public bool DeleteTraing(int id)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"Delete from tblTraining where Id='" + id + "'";
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Failed to Delete Training Information");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public Training GetTraining(int id)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"select * from tblTraining where Id='"+id+"'";
                SqlDataReader reader = CommandObj.ExecuteReader();
                Training training = new Training();
                while (reader.Read())
                {
                    training.Id = Convert.ToInt32(reader[0]);
                    training.Name = reader[1].ToString();
                    training.Department = reader[2].ToString();
                    training.Trainer = reader[3].ToString();
                    training.Topics = reader[4].ToString();
                    training.Venue = reader[5].ToString();
                    DateTime sdt = Convert.ToDateTime(reader[6]);
                    training.StartDate = sdt.ToShortDateString();
                    DateTime edt = Convert.ToDateTime(reader[7]);
                    training.EndDate = edt.ToShortDateString();
                    training.Description = reader[8].ToString();
                   
                }
                return training;
            }
            catch (Exception)
            {
                throw new Exception("Error occured during Retriving Training Information");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public bool AssignEmployeeToTraining(int empId, TrainedEmployee trainee)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format(@"insert into tblTrainee values(@empid,@triningid)");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@empid", SqlDbType.Int).Value = empId;
                CommandObj.Parameters.Add("@triningid", SqlDbType.Int).Value = trainee.Training.Id;
                CommandObj.ExecuteNonQuery();
                return true;
             }
            catch (Exception)
            {
                throw new Exception("Error Occured During Assign Trainee to Training Module !");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public bool IsTraineeAlreadyAssigned(int empId, TrainedEmployee trainee)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"select * from tblTrainee where EmpId='" + empId + "' and TrainingId='"+trainee.Training.Id+"'";
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    return true;
                }
                return false;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured During Assign Trainee to Training Module !");
            }
            finally
            {
                ConnectionObj.Close();
            }
         }

        public List<ReportTrainedEmployee> GetAllTrainedEmployee(int trainingId)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format(@"SELECT  tblCode.CodeName,tblEmployees.Name, tblEmployees.Status, tblDesignations.Title,tblProjects.ProjectName, tblTraining.Name,tblTraining.Id
                                        FROM tblCode INNER JOIN
                                        tblEmployees ON tblCode.Id = tblEmployees.Code_CodeID full outer JOIN
                                        tblDesignations ON tblEmployees.DesignationID = tblDesignations.Id full outer JOIN
                                        tblProjects ON tblEmployees.ProjectID = tblProjects.ProjectID full outer JOIN
                                        tblTrainee ON tblEmployees.EmployeeId = tblTrainee.EmpId INNER JOIN
                                        tblTraining ON tblTrainee.TrainingId = tblTraining.Id
                                        where tblTraining.Id='" +trainingId+"'");
                SqlDataReader reader = CommandObj.ExecuteReader();
                List<ReportTrainedEmployee> trainedEmployees = new List<ReportTrainedEmployee>();
                while (reader.Read())
                {
                    ReportTrainedEmployee trainedEmployee=new ReportTrainedEmployee();
                    trainedEmployee.Code = reader[0].ToString();
                    trainedEmployee.Name = reader[1].ToString();
                    trainedEmployee.EmpStatus = reader[2].ToString();
                    trainedEmployee.Designation = reader[3].ToString();
                    trainedEmployee.Project = reader[4].ToString();
                    trainedEmployee.Training = reader[5].ToString();
                    trainedEmployee.TrainingId = Convert.ToInt32(reader[6]);
                    trainedEmployees.Add(trainedEmployee);
                }
                return trainedEmployees;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured during Reading Information");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }
    }
}